﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


  namespace 学生成绩查询及选课系统
    {
      class DBAccess
      {
          public static SqlCommand sqlcmd = null;
          public static SqlDataAdapter DataAdapter = null;
          //public static DataSet1 ds1 ;
          static DataSet1TableAdapters.studentTableAdapter adaStu = new DataSet1TableAdapters.studentTableAdapter();

          static DataSet1TableAdapters.grade1TableAdapter adaGrage = new DataSet1TableAdapters.grade1TableAdapter();

          public static int grade_Update(int score, string status, int stuno ,int cno)
          {
              return adaGrage.gradeUpdate(score, status, stuno, cno);
          }


          /// <summary>
          /// 用于删除、插入、更新 操作
          /// 无sql参数 返回的操作状态  有错误或没有结果 返回 0
          /// </summary>
          /// <returns></returns>
          /// 

          //public static int stu_Ins(string uname,int gender,int age,string classno,string status)
          //{
          //    return adaStu.addStudent(uname, gender, age, classno, status);
          //}


          public static int GetState(string sql)
          {
              int rs = 0;
              using (sqlcmd = new SqlCommand(sql, Conndb.getconn()))
              {
                  try
                  {
                      rs = sqlcmd.ExecuteNonQuery();
                  }
                  catch (Exception)
                  {

                      return 0;
                  }

              }
              return rs;
          }

          /// <summary>
          /// 用于删除、插入、更新 操作
          /// 带入sql参数 返回的操作状态   有错误或没有结果 返回 0
          /// </summary>
          /// <param name="sql">sql字符串</param>
          /// <param name="SqlPArry">参数化查询的参数</param>
          /// <returns></returns>
          public static int GetState(string sql, SqlParameter[] SqlPArry)
          {
              int rs = 0;
              using (sqlcmd = new SqlCommand(sql, Conndb.getconn()))
              {
                  sqlcmd.Parameters.AddRange(SqlPArry);
                  try
                  {

                      rs = sqlcmd.ExecuteNonQuery();
                  }
                  catch (Exception)
                  {

                      return 0;
                  }

              }
              return rs;

          }
          /// <summary>
          /// 
          /// 返回 操作结果的第一个值（单值）
          /// </summary>
          /// <param name="sql"></param>
          /// <returns></returns>

          public static object GetOne(string sql)
          {
              object rs = null;
              using (sqlcmd = new SqlCommand(sql, Conndb.getconn()))
              {
                  try
                  {
                      rs = sqlcmd.ExecuteScalar();
                  }
                  catch (Exception)
                  {
                      return null;
                  }
              }

              return rs;
          }

          /// <summary>
          /// 返回 操作结果的第一个值,带sql参数的
          /// 无结果，返回空
          /// </summary>
          /// <param name="sql">sql语句</param>
          /// <param name="SqlPArry">参数绑定的数组</param>
          /// <returns></returns>
          public static object GetOne(string sql, SqlParameter[] SqlPArry)
          {
              object rs = null;
              using (sqlcmd = new SqlCommand(sql, Conndb.getconn()))
              {
                  sqlcmd.Parameters.AddRange(SqlPArry);
                  try
                  {
                      rs = sqlcmd.ExecuteScalar();
                  }
                  catch (Exception)
                  {
                      return null;
                  }
              }

              return rs;
          }


          /// <summary>
          /// 返回数据的DataReader 不带参数  
          ///  错误返回空！
          /// </summary>
          /// <param name="sql">sql语句</param>
          /// <returns></returns>
          public static SqlDataReader GetReader(string sql)
          {
              SqlDataReader reDate = null;
              using (sqlcmd = new SqlCommand(sql, Conndb.getconn()))
              {
                  try
                  {
                      reDate = sqlcmd.ExecuteReader();
                  }
                  catch (Exception)
                  {

                      return null;
                  }
              }

              return reDate;
          }



          /// <summary>
          /// 返回数据的DataReader，带查询参数
          /// 错误返回空！
          /// </summary>
          /// <param name="sql">sql语句</param>
          /// <param name="SqlPArry">参数绑定的数组</param>
          /// <returns></returns>
          public static SqlDataReader GetReader(string sql, SqlParameter[] SqlPArry)
          {
              SqlDataReader reDate = null;
              using (sqlcmd = new SqlCommand(sql, Conndb.getconn()))
              {
                  sqlcmd.Parameters.AddRange(SqlPArry);
                  try
                  {
                      reDate = sqlcmd.ExecuteReader();
                  }
                  catch (Exception ex)
                  {
                      return null;
                  }
              }

              return reDate;
          }

          /// <summary>
          /// 获取数据根据sql语句 返回一个整张表
          /// </summary>
          /// <param name="sql"></param>
          /// <returns></returns>
          public static DataTable GetTable(string sql)
          {
              DataTable dt = new DataTable();
              using (DataAdapter = new SqlDataAdapter(sql, Conndb.getconn()))
              {
                  DataAdapter.Fill(dt);
                  return dt;
              }
          }

          /// <summary>
          /// 
          /// </summary>
          /// <param name="sql"></param>
          /// <param name="SqlPArry"></param>
          /// <returns></returns>
          public static DataTable GetTable(string sql, SqlParameter[] SqlPArry)
          {
              DataTable dt = new DataTable();
              using (DataAdapter = new SqlDataAdapter(sql, Conndb.getconn()))
              {
                  DataAdapter.SelectCommand.Parameters.AddRange(SqlPArry);
                  DataAdapter.Fill(dt);
                  return dt;
              }
          }

          public static bool isNullOrEmpty(params string[] arg)
          {
              foreach (string str in arg)
              {
                  if (str.Trim() == "" || str == null)
                  {
                      return true;
                  }
              }
              return false;
          }

          public static DataSet RunProcedure(string storedProcName,
            IDataParameter[] parameters, int Times = 30)
          {
              using (SqlConnection connection = Conndb.getconn())
              {
                  DataSet dataSet = new DataSet();
                  //connection.Open();
                  SqlDataAdapter sqlDA = new SqlDataAdapter();
                  sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                  sqlDA.SelectCommand.CommandTimeout = Times;
                  sqlDA.Fill(dataSet);
                  connection.Close();
                  return dataSet;

              }
          }
          public static int RunProcedureExecuteNonQuery(string storedProcName,
              IDataParameter[] parameters, int Times = 30)
          {
              using (SqlConnection connection = Conndb.getconn())
              {
                  DataSet dataSet = new DataSet();
                  //connection.Open();
                  //SqlDataAdapter sqlDA = new SqlDataAdapter();
                  SqlCommand cmd = BuildQueryCommand(connection, storedProcName, parameters);
                  //sqlDA.SelectCommand.CommandTimeout = Times;
                  //sqlDA.Fill(dataSet);
                  //connection.Close();
                  int res = cmd.ExecuteNonQuery();

                  return res;

              }
          }
          /// <summary>
          /// 构建SqlCommand对象
          /// </summary>
          /// <param name="connection">数据库连接名</param>
          /// <param name="storeProcName"></param>
          /// <param name="parameters">存储过程参数</param>
          /// <returns></returns>
          private static SqlCommand BuildQueryCommand(SqlConnection connection, string storeProcName, IDataParameter[] parameters)
          {
              SqlCommand command = new SqlCommand(storeProcName, connection);
              command.CommandType = CommandType.StoredProcedure;
              if (parameters != null && parameters.Length > 0)
              {
                  foreach (SqlParameter parameter in parameters)
                  {
                      if (parameter != null)
                      {
                          if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null))
                          {
                              parameter.Value = DBNull.Value;
                          }
                          command.Parameters.Add(parameter);
                      }
                  }
              }
              return command;
          }


      }

    }


